﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
///db_users 的摘要说明
/// </summary>
public class db_users
{
    SqlDBClass db;
    public db_users()
    {
        db = new SqlDBClass();
    }

    /// <summary>
    /// 增加用户
    /// </summary>
    /// <param name="username"></param>
    /// <param name="pws"></param>
    /// <param name="gold"></param>
    /// <param name="email"></param>
    /// <param name="sex"></param>
    /// <param name="status"></param>
    /// <returns></returns>
    public Boolean addUser(string UserName, string pwd, string Gold, string Email, string Sex, string status)
    {
        string strsql = "Insert Into [user](UserName,pwd,Gold,regTime,Email,Sex,status) Values('" + UserName + "','" + Tools.mymd5(pwd) + "'," + Gold + ",'" + DateTime.Now + "','" + Email + "','" + Sex + "','" + status + "')";
        return db.execSqlNoQuery(strsql);
    }


    /// <summary>
    /// 更新用户
    /// </summary>
    /// <param name="id"></param>
    /// <param name="turename"></param>
    /// <param name="sex"></param>
    /// <param name="faculty"></param>
    /// <param name="profession"></param>
    /// <param name="mobilenumber"></param>
    /// <param name="total"></param>
    /// <returns></returns>
    public Boolean updateUser(string userID, string username, string email, string sex, string status)
    {
        string strsql = "update [user] set username='" + username + "',email='" + email + "',sex='" + sex + "',status=" + status + " where userID=" + userID;
        return db.execSqlNoQuery(strsql);
    }



    /// <summary>
    /// 查询所有用户
    /// </summary>
    /// <returns></returns>
    public DataSet selectUserALL()
    {
        string strsql = "select * from [user] order by userID desc";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 查询所有用户
    /// </summary>
    /// <returns></returns>
    public DataSet selectUserALL_normal()
    {
        string strsql = "select * from [user] where status=0 order by userID desc";
        return db.execSql(strsql);
    }


    /// <summary>
    /// 根据id，查询用户
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public DataSet selectUserONE_id(string userID)
    {
        string strsql = "select * from [user] where userID=" + userID;
        return db.execSql(strsql);
    }



    /// <summary>
    /// 根据用户名，查询用户
    /// </summary>
    /// <param name="turename"></param>
    /// <returns></returns>
    public DataSet selectUserONE_username(string username)
    {
        string strsql = "select * from [user] where username='" + username + "'";
        return db.execSql(strsql);
    }


    /// <summary>
    /// 根据ID检查密码是否正确
    /// </summary>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean checkPwd_id(string userID, string pwd)
    {
        string sql = "select * from [user] where userID='" + userID + "' and pwd='" + pwd + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    /// <summary>
    /// 根据用户名检查密码是否正确
    /// </summary>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean checkPwd_username(string username, string pwd)
    {
        string sql = "select * from [user] where username='" + username + "' and pwd='" + pwd + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    /// <summary>
    /// 根据ID重设密码
    /// </summary>
    /// <param name="userID"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean setPwd_id(string userID, string pwd)
    {
        string strsql = "update [user] set pwd='" + pwd + "'where  userID=" + userID;
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 根据用户名重设密码
    /// </summary>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean setPwd_username(string username, string pwd)
    {
        string strsql = "update [user] set pwd='" + pwd + "'where  username='" + username + "'";
        return db.execSqlNoQuery(strsql);
    }


    /// <summary>
    /// 用户名是否存在
    /// </summary>
    /// <param name="username"></param>
    /// <returns>true：存在；false：不存在</returns>
    public Boolean isExistName(string username)
    {
        string sql = "select * from [user] where username='" + username + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    /// <summary>
    /// 获取最新用户编号
    /// </summary>
    /// <returns></returns>
    public string getNewUserId()
    {
        string strsql = "select  coalesce(nullif(max(userId),''),'0')+1 as userId  from [user]";
        DataSet ds = new DataSet();
        string userId = "";
        ds = db.execSql(strsql);

        if (ds.Tables.Count != 0)
        {
            userId = ds.Tables[0].Rows[0]["userId"].ToString();
        }

        return userId.Equals("") ? "1" : userId; //如果数据库为空，则显示为1
    }



    public DataSet selectUser_search(string userId, string userName, string email, string status)
    {
        string strsql = "select* from [user] where ";
        string strWhere = "userID like '%" + userId + "%'";
        strWhere += " and userName like '%" + userName + "%'";
        strWhere += " and Email like '%" + email + "%'";
        if (!status.Equals("all"))
        {
            strWhere += " and status =" + status + "";
        }
        strsql += strWhere;
        return db.execSql(strsql);
    }

    public string[] isLogin(string username, string pwd)
    {
        string[] info = new string[4];
        DataSet ds = checkPwdDS(username, pwd);
        if (ds != null)
        {
            info[0] = ds.Tables[0].Rows[0]["UserID"].ToString();
            info[1] = ds.Tables[0].Rows[0]["UserName"].ToString();
            info[2] = ds.Tables[0].Rows[0]["lastTime"].ToString();
            info[3] = ds.Tables[0].Rows[0]["sex"].ToString();
            //info[3] = ds.Tables[0].Rows[0][3].ToString();
            // info[4] = ds.Tables[0].Rows[0][4].ToString();
            if (ds.Tables[0].Rows[0]["status"].ToString().Equals("1"))
            {
                info[0] = "noPass";
                return info;
            }
            else
            {
                if (updateTimeIP(username))
                    return info;
                else
                    return null;
            }
        }
        else
        {
            return null;
        }
    }
    public DataSet checkPwdDS(string username, string pwd)
    {
        string sql = "select * from [user] where UserName='" + username + "' and pwd='" + pwd + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return ds;
        }
        else
        {
            return null;
        }
    }

    public Boolean updateTimeIP(string name)
    {
        string strsql = "update [user] set lastIP='" + HttpContext.Current.Request.UserHostAddress + "',lastTime='" + DateTime.Now + "'where  UserName='" + name + "'";
        return db.execSqlNoQuery(strsql);
    }

    public DataSet selectUserCourseSell(string userid)
    {
        string strsql = "select c.courseID,c.courseName,ct.typeName,t.teacherName,c.courseHour,c.gold,cs.[time] from [user] u,coursesell cs,course c ,coursetype ct,teacher t where u.userid=cs.userid  and cs.courseid=c.courseid and t.teacherid=c.teacherid and ct.typeid=c.courseType and c.status = '0' and t.status = '0' and u.userid='" + userid + "'";
        return db.execSql(strsql);
    }

    

}